package cn.cjxy.userms.dao;

import cn.cjxy.userms.entity.User;
import cn.cjxy.userms.utlis.DataSourceUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    //提前声明
    Connection connection;
    PreparedStatement preparedStatement;
    ResultSet resultSet;
    /**
     * 根据用户名和密码查询数据库
     * @param username
     * @param password
     * @return
     */
   public User  findUserByusername(String username,String password){

       try {  //先获取数据库连接
           connection = DataSourceUtils.getConnection();
           //sql语句
           String sql = "select * from t_user where username=? and password=?";
           //创建预编译语句对象
           preparedStatement = connection.prepareStatement(sql);
           //给问号赋值
           preparedStatement.setString(1, username);//给第1个问号传的参数是username  String类型
           preparedStatement.setString(2, password);
           //执行查询语句，获取结果集
            resultSet = preparedStatement.executeQuery();
           //创建user对象存值
           User user = new User();
           //如果有记录
           while (resultSet.next()) {
               //resultSet.getXx("aa")   Xx字段类型  aa：数据库的字段名
               user.setId(resultSet.getInt("id"));
               user.setUsername(resultSet.getString("username"));
               user.setPassword(resultSet.getString("password"));
               user.setGender(resultSet.getInt("gender"));
               user.setAge(resultSet.getInt("age"));
               user.setAddress(resultSet.getString("address"));
               //返回user对象
               return user;
           }
           System.out.println(user);
       } catch (SQLException e) {
           throw new RuntimeException(e);
       }finally {
           //关流
           DataSourceUtils.close(connection,preparedStatement,resultSet);
       }
       return null;
   }

    /**
     * 查询所有
     * @return
     */
    public List<User> findAll() {
        try {
            connection = DataSourceUtils.getConnection();
            //sql语句
            String sql = "select * from t_user";
            //创建预编译语句对象
             preparedStatement = connection.prepareStatement(sql);
            //执行查询语句，获取结果集
             resultSet = preparedStatement.executeQuery();
            //查询的结果是一个list集合  将查询的数据封装到集合中
            List<User> users = new ArrayList<>();
            while (resultSet.next()){
                 //准备user对象来装单行的数据
                User user = new User();
                //分别设置值
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setGender(resultSet.getInt("gender"));
                user.setAge(resultSet.getInt("age"));
                user.setAddress(resultSet.getString("address"));
                //将user存入list中
                users.add(user);
            }
             return users;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DataSourceUtils.close(connection,preparedStatement,resultSet);
        }
    }

    /**
     * 添加用户
     * @param user
     * @return
     */
    public int add(User user) {
        try {
            connection = DataSourceUtils.getConnection();
            //sql语句
            String sql = "insert into  t_user values (null,?,?,?,?,?)";
            //创建预编译语句对象
            preparedStatement = connection.prepareStatement(sql);
            //给问号赋值
            preparedStatement.setString(1, user.getUsername());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setInt(3, user.getGender());
            preparedStatement.setInt(4, user.getAge());
            preparedStatement.setString(5, user.getAddress());
            //执行新增语句
            int i = preparedStatement.executeUpdate();
            // i有两种结果  成为1   失败0
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DataSourceUtils.close(connection,preparedStatement,null);
        }

    }

    /**
     * 修改用户
     * @param user
     * @return
     */
    public int update(User user) {
        try {
            connection = DataSourceUtils.getConnection();
            //sql语句
            String sql = "update t_user set username=?,password=?,gender=?,age=?,address=? where id=?";
            //创建预编译语句对象
            preparedStatement = connection.prepareStatement(sql);
            //给问号赋值
            preparedStatement.setString(1, user.getUsername());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setInt(3, user.getGender());
            preparedStatement.setInt(4, user.getAge());
            preparedStatement.setString(5, user.getAddress());
            preparedStatement.setInt(6, user.getId());
            //执行新增语句
            int i = preparedStatement.executeUpdate();
            // i有两种结果  成为1   失败0
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DataSourceUtils.close(connection,preparedStatement,null);
        }

    }

    /**
     * 根据id删除用户
     * @param id
     * @return
     */
    public int delete(int id) {
        try {
            connection = DataSourceUtils.getConnection();
            //sql语句
            String sql = "delete from t_user where id=?";
            //创建预编译语句对象
            preparedStatement = connection.prepareStatement(sql);
            //给问号赋值
            preparedStatement.setInt(1, id);
            //执行新增语句
            int i = preparedStatement.executeUpdate();
            // i有两种结果  成为1   失败0
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DataSourceUtils.close(connection,preparedStatement,null);
        }
    }
    //思考 列表页添加条件查询---怎么实现

    /**
     * 根据id查询用户
     * @param id
     * @return
     */
    public User findUserById(int id) {
        try {
            connection = DataSourceUtils.getConnection();
            //sql语句
            String sql = "select * from t_user where id=?";
            //创建预编译语句对象
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            //执行查询语句，获取结果集
            resultSet = preparedStatement.executeQuery();
            //准备user对象来装单行的数据
            User user = new User();
            while (resultSet.next()){
                //分别设置值
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setGender(resultSet.getInt("gender"));
                user.setAge(resultSet.getInt("age"));
                user.setAddress(resultSet.getString("address"));
            }
            return user;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DataSourceUtils.close(connection,preparedStatement,resultSet);
        }
    }
}
